{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import numpy as np \n",
    "import os\n",
    "hx_bank = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\广东华兴银行\"\n",
    "nb_bank = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\宁波银行\"\n",
    "# 遍历广东华兴银行的数据\n",
    "hx_df = pd.read_excel(r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\广东华兴银行\\210000079959深圳市元视界科技有限公司-广东华兴银行.xlsx\",sheet_name=\"Sheet2\",dtype=str)\n",
    "hx_df['数据来源'] = \"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\广东华兴银行\\210000079959深圳市元视界科技有限公司-广东华兴银行.xlsx\"\n",
    "hx_df['本方开户行'] = \"广东华兴银行\"\n",
    "hx_df.rename(columns={'账号':'本方账号','对方账户所属银行':'对手开户银行'},inplace=True)\n",
    "# 遍历宁波银行的数据\n",
    "nb_df = pd.DataFrame()\n",
    "for root,dirs,files in os.walk(nb_bank):\n",
    "    for file in files:\n",
    "        file_path = os.path.join(root,file)\n",
    "        df = pd.read_excel(file_path,dtype=str)\n",
    "        df['交易日期/时间'] = pd.to_datetime(df['交易日期'], format='%Y%m%d').dt.strftime('%Y-%m-%d')+' '+df['交易时间']\n",
    "        df['本方开户行'] = '宁波银行'\n",
    "        df['数据来源'] = file_path\n",
    "        nb_df = pd.concat([nb_df,df])\n",
    "#nb_df.to_excel(r'D:\\jupyter\\python\\银行流水数据处理\\2024-08-30\\宁波银行银行交易流水数据.xlsx',index = False)\n",
    "nb_df = nb_df.drop(columns={'交易日期','交易时间','子账号'})\n",
    "nb_df.to_excel('./宁波银行银行交易流水数据.xlsx',index=False)\n",
    "hx_df.to_excel('./广东华兴银行银行交易流水数据.xlsx',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4525 entries, 0 to 4524\n",
      "Data columns (total 45 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   序号            4525 non-null   object\n",
      " 1   *交易日期         4525 non-null   object\n",
      " 2   *交易时间         4525 non-null   object\n",
      " 3   *交易行名称        4525 non-null   object\n",
      " 4   *公私标识         4525 non-null   object\n",
      " 5   *客户号          4525 non-null   object\n",
      " 6   *证件号码         4525 non-null   object\n",
      " 7   *账号           4525 non-null   object\n",
      " 8   卡号            4525 non-null   object\n",
      " 9   *账户名称         4525 non-null   object\n",
      " 10  账户类型          4525 non-null   object\n",
      " 11  账户类别          4525 non-null   object\n",
      " 12  交易对手方账户类别     4525 non-null   object\n",
      " 13  交易对方行代码       4525 non-null   object\n",
      " 14  交易对方行名称       4525 non-null   object\n",
      " 15  交易对方账号（卡号）    4525 non-null   object\n",
      " 16  交易对方户名        4525 non-null   object\n",
      " 17  *资金收付标识       4525 non-null   object\n",
      " 18  *现金、转账标识      4525 non-null   object\n",
      " 19  *币种           4525 non-null   object\n",
      " 20  *原币种交易金额      4525 non-null   object\n",
      " 21  原币种交易金额（万元）   4525 non-null   object\n",
      " 22  折美元交易金额       4525 non-null   object\n",
      " 23  折美元交易金额（万元）   4525 non-null   object\n",
      " 24  折人民币交易金额      4525 non-null   object\n",
      " 25  折人民币交易金额（万元）  4525 non-null   object\n",
      " 26  *账户余额         4525 non-null   object\n",
      " 27  账户余额（万元）      4525 non-null   object\n",
      " 28  代理交易标识        4525 non-null   object\n",
      " 29  代理人姓名         4525 non-null   object\n",
      " 30  代理人联系方式       4525 non-null   object\n",
      " 31  代理人身份证件种类     4525 non-null   object\n",
      " 32  代理人身份证件号码     4525 non-null   object\n",
      " 33  业务流水号         4525 non-null   object\n",
      " 34  柜员号           4525 non-null   object\n",
      " 35  业务名称          4525 non-null   object\n",
      " 36  冲账标识          4525 non-null   object\n",
      " 37  摘要说明          4525 non-null   object\n",
      " 38  跨境交易标识        4525 non-null   object\n",
      " 39  交易对方所在国家或地区   4525 non-null   object\n",
      " 40  *交易方式标识       4525 non-null   object\n",
      " 41  IP地址          4525 non-null   object\n",
      " 42  ATM机具编号       4525 non-null   object\n",
      " 43  ATM机具所属行行号    4525 non-null   object\n",
      " 44  MAC或IMEI地址    3705 non-null   object\n",
      "dtypes: object(45)\n",
      "memory usage: 1.6+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# 获取元视界交易明细表\n",
    "import pandas as pd \n",
    "import numpy as np \n",
    "import os\n",
    "target_path = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\元视界\\交易明细表.xls\"\n",
    "df = pd.read_excel(target_path,dtype=str,sheet_name=\"交易明细表\",header=1)\n",
    "print(df.info())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "客户名称:深圳嘉银基金管理有限公司，客户编号:411830494847393411，客户账号:44201508000052585368，存款合约序号:0，币别:人民币元，钞汇标志:钞\n",
      "深圳嘉银基金管理有限公司\n",
      "客户名称:深圳市中融富邦投资管理有限公司，客户编号:682820494346766682，客户账号:44201505900052544064，存款合约序号:0，币别:人民币元，钞汇标志:钞\n",
      "深圳市中融富邦投资管理有限公司\n",
      "客户名称:黑风国际投资（深圳）有限责任公司，客户编号:060841000001188823，客户账号:44250100017600000272，存款合约序号:0，币别:人民币元，钞汇标志:钞\n",
      "黑风国际投资（深圳）有限责任公司\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "js_path = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国建设银行\"\n",
    "js_df = pd.DataFrame()\n",
    "for root,dirs,files in os.walk(js_path):\n",
    "    for file in files:\n",
    "        if file.endswith(\".xlsx\"):\n",
    "            file_path = os.path.join(root,file)\n",
    "            df = pd.read_excel(file_path,dtype=str,sheet_name=\"企业活期明细信息\",header=9)\n",
    "            df2 = pd.read_excel(file_path,dtype=str,sheet_name=\"企业活期明细信息\",header=8)\n",
    "            benf_str = df2.columns[0]\n",
    "            print(benf_str)\n",
    "            print(benf_str.split(\"客户名称:\")[1].split(\"，\")[0])\n",
    "            df['本方户名'] = benf_str.split(\"客户名称:\")[1].split(\"，\")[0]\n",
    "            df['客户编号'] = benf_str.split(\"客户编号:\")[1].split(\"，\")[0]\n",
    "            df['本方账号'] = benf_str.split(\"客户账号:\")[1].split(\"，\")[0]\n",
    "            df['本方开户行'] = '中国建设银行'\n",
    "            df['交易日期/时间'] = pd.to_datetime(df['交易日期'], format='%Y-%m-%d').dt.strftime('%Y-%m-%d')+' '+df['交易时间']\n",
    "            df['数据来源'] = file_path\n",
    "            js_df =  pd.concat([js_df,df])\n",
    "js_df.drop(columns={'交易日期','交易时间'},inplace=True)\n",
    "js_df.to_excel('./建设银行交易流水数据.xlsx',index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 502 entries, 0 to 501\n",
      "Data columns (total 40 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   客户号           502 non-null    object\n",
      " 1   客户类型          502 non-null    object\n",
      " 2   客户名           502 non-null    object\n",
      " 3   账号            502 non-null    object\n",
      " 4   账户行           502 non-null    object\n",
      " 5   交易日期          502 non-null    object\n",
      " 6   交易时间          502 non-null    object\n",
      " 7   交易类型          502 non-null    object\n",
      " 8   交易类型描述        502 non-null    object\n",
      " 9   交易币种          502 non-null    object\n",
      " 10  交易金额          502 non-null    object\n",
      " 11  交易金额（折合美元）    502 non-null    object\n",
      " 12  交易金额（折合人民币）   502 non-null    object\n",
      " 13  交易后账户余额       502 non-null    object\n",
      " 14  交易对手姓名        502 non-null    object\n",
      " 15  交易对手账号        502 non-null    object\n",
      " 16  交易对手证件号码      90 non-null     object\n",
      " 17  交易对手所在机构      502 non-null    object\n",
      " 18  交易对手地址        90 non-null     object\n",
      " 19  交易对手银行名称      502 non-null    object\n",
      " 20  资金用途和来源       502 non-null    object\n",
      " 21  跨境标识          502 non-null    object\n",
      " 22  柜员号           502 non-null    object\n",
      " 23  交易类型系统编码      502 non-null    object\n",
      " 24  状态码           502 non-null    object\n",
      " 25  交易状态          502 non-null    object\n",
      " 26  ATM编号         0 non-null      object\n",
      " 27  代办人名称         0 non-null      object\n",
      " 28  代办人地址         0 non-null      object\n",
      " 29  代办人证件类型       0 non-null      object\n",
      " 30  代办人证件号码       0 non-null      object\n",
      " 31  发生交易的银行卡卡号类型  0 non-null      object\n",
      " 32  发生交易的银行卡卡号    0 non-null      object\n",
      " 33  收付款方匹配号类型     117 non-null    object\n",
      " 34  收付款方匹配号       117 non-null    object\n",
      " 35  非柜台交易方式       221 non-null    object\n",
      " 36  ip地址          264 non-null    object\n",
      " 37  mac地址         226 non-null    object\n",
      " 38  终端标识          226 non-null    object\n",
      " 39  操作系统信息        264 non-null    object\n",
      "dtypes: object(40)\n",
      "memory usage: 157.0+ KB\n",
      "None\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\全民国际\\前海全民国际投资(深圳)有限公司20161115-20240715对账单.xlsx\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\Administrator\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\博弈视界\\深圳博弈视界科技有限公司20190930-20240708对账单-中国银行深圳新安支行.xlsx\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\Administrator\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:226: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_17092\\4274901879.py:32: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value '中国建设银行总行' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.\n",
      "  zg_excle_df.loc[index,'对手开户银行'] = row['真实收款人所属机构名称']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\全民国际\\752368189421(20161115-20240708).xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\博弈视界\\761472810760(20190930-20240708).xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\轩瑞资本\\深圳市轩瑞资本管理有限公司761465413819.xlsx\n"
     ]
    }
   ],
   "source": [
    "# 合并中国银行数据 \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "zg_path = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\"\n",
    "# 合并pdf的所有excel文件\n",
    "pdf_files = ['761472810760(20190930-20240708).xlsx','752368189421(20161115-20240708).xlsx','深圳市轩瑞资本管理有限公司761465413819.xlsx']\n",
    "excel_files = ['深圳博弈视界科技有限公司20190930-20240708对账单-中国银行深圳新安支行.xlsx','前海全民国际投资(深圳)有限公司20161115-20240715对账单.xlsx']\n",
    "# 处理最后一个excel文件\n",
    "zg_excel_last_df = pd.read_excel(r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\轩瑞资本\\深圳市轩瑞资本管理有限公司761473345519.xlsx\",dtype=str)\n",
    "print(zg_excel_last_df.info())\n",
    "zg_excel_last_df['交易日期/时间'] = pd.to_datetime(zg_excel_last_df['交易日期'], format='%Y%m%d').dt.strftime('%Y-%m-%d')+' '+zg_excel_last_df['交易时间']\n",
    "zg_excel_last_df['数据来源'] = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国银行\\轩瑞资本\\深圳市轩瑞资本管理有限公司761473345519.xlsx\"\n",
    "zg_excel_last_df['本方开户行'] = '中国银行'\n",
    "zg_df = pd.DataFrame()\n",
    "zg_excle_df = pd.DataFrame()\n",
    "for root,dirs,files in os.walk(zg_path):\n",
    "    for file in files:\n",
    "        if file in excel_files:\n",
    "            excel_file_path = os.path.join(root,file)\n",
    "            print(excel_file_path)\n",
    "            df = pd.read_excel(excel_file_path,dtype=str)\n",
    "            df['数据来源'] = excel_file_path\n",
    "            zg_excle_df =  pd.concat([zg_excle_df,df])\n",
    "# 处理真实付款人账户所属机构名称 以及真实收款人所属机构名称\n",
    "zg_excle_df['本方开户行'] = '' \n",
    "zg_excle_df['对方开户行'] = ''\n",
    "for index,row in zg_excle_df.iterrows():\n",
    "    flag = row['借贷标识']\n",
    "    if flag == '支出':\n",
    "        zg_excle_df.loc[index,'本方开户行'] = row['真实付款人账户所属机构名称']\n",
    "        zg_excle_df.loc[index,'对手开户银行'] = row['真实收款人所属机构名称']\n",
    "    if flag == '收入':\n",
    "        zg_excle_df.loc[index,'本方开户行'] = row['真实收款人所属机构名称']\n",
    "        zg_excle_df.loc[index,'对手开户银行'] = row['真实付款人账户所属机构名称']\n",
    "# 更改excle的字段 客户名称\t记账日\t交易类型\t交易流水号\t借贷标识\t发生额\t余额\t可用余额\t对方账号\t对方账号名称\t用途\t\t附言\t摘要\n",
    "\n",
    "zg_excle_df = zg_excle_df[['客户名称','记账日','交易类型','交易流水号','借贷标识','发生额','余额','对方账号','对方账号名称','用途','附言','摘要','本方开户行','对手开户银行','数据来源','账号',]]\n",
    "zg_excle_df['附言'] = zg_excle_df['附言'].fillna(zg_excle_df['用途'])\n",
    "#客户名\t账号\t账户行\t交易日期\t交易时间\t交易类型\t交易币种\t交易金额\t交易后账户余额\t交易对手姓名\t交易对手账号\t交易对手账号\t交易对手证件号码\t交易对手所在机构\t交易对手地址\t交易对手银行名称\t资金用途和来源\tip地址\tmac地址\t终端标识\t操作系统信息\n",
    "zg_excel_last_df = zg_excel_last_df[['客户名','账号','账户行','交易日期/时间','交易类型','交易币种','交易金额','交易后账户余额','交易对手姓名','交易对手账号','交易对手证件号码','交易对手所在机构','资金用途和来源','ip地址','mac地址','终端标识','操作系统信息','数据来源','本方开户行']]\n",
    "#zg_excle_df = zg_excle_df.rename(columns={'':'','':'','':'','':'','':'','':'','':'','':'','':'','':'','':'','':'','':'','':'','':''})  \n",
    "zg_df = pd.DataFrame()\n",
    "for root,dirs,files in os.walk(zg_path):\n",
    "    for file in files:\n",
    "        if file in pdf_files:\n",
    "            excel_file_path = os.path.join(root,file)\n",
    "            print(excel_file_path)\n",
    "            df = pd.read_excel(excel_file_path,sheet_name=\"excel表格数据\",dtype=str)\n",
    "            df['对手账号'] = ''\n",
    "            df['对手开户银行'] = ''\n",
    "            df['对手姓名'] = ''\n",
    "            df['摘要'] = ''\n",
    "            df['数据来源'] = excel_file_path.replace('.xlsx','.pdf')\n",
    "            df['本方开户行'] = '中国银行'\n",
    "            #print(df.info())\n",
    "            for index,rows in df.iterrows():\n",
    "                # 对方信息\n",
    "                message = rows['对方信息']\n",
    "                # 对信息进行数据分类汇总\n",
    "                message_list = str(message).split('/')\n",
    "                message_list = [item for item in message_list if item != '']\n",
    "                # 对手账号\t对手开户银行\t对手姓名\t摘要 \n",
    "                if len(message_list) >= 4:\n",
    "                    if any(\"银行\" in item for item in message_list):\n",
    "                        if message_list[0].find(\"银行\")!=-1:\n",
    "                            df.loc[index,'对手开户银行'] = message_list[0]\n",
    "                            df.loc[index,'对手账号'] = message_list[1]\n",
    "                            df.loc[index,'摘要'] = ''.join(set(message_list[2:]))\n",
    "                        else:\n",
    "                            df.loc[index,'对手姓名'] = message_list[0]\n",
    "                            df.loc[index,'对手开户银行'] = message_list[1]\n",
    "                            df.loc[index,'对手账号'] = message_list[2]\n",
    "                            df.loc[index,'摘要'] = ''.join(set(message_list[3:]))\n",
    "                    else:\n",
    "                        df.loc[index,'对手姓名'] = message_list[0]\n",
    "                        df.loc[index,'对手账号'] = message_list[1]\n",
    "                        df.loc[index,'摘要'] = ''.join(set(message_list[2:]))\n",
    "                if len(message_list) == 3:\n",
    "                    if message_list[0].find(\"银行\")!=-1:\n",
    "                        df.loc[index,'对手姓名'] = message_list[0]\n",
    "                        df.loc[index,'对手开户银行'] = message_list[1]\n",
    "                        df.loc[index,'对手账号'] = message_list[2]\n",
    "                    else:\n",
    "                        df.loc[index,'对手姓名'] = message_list[0]\n",
    "                        df.loc[index,'摘要'] = ''.join(set(message_list[1:]))\n",
    "                if len(message_list) == 2:\n",
    "                    #print(\"\")\n",
    "                    if message_list[0].find(\"银行\")!=-1:\n",
    "                       df.loc[index,'对手开户银行'] = message_list[0]\n",
    "                    if message_list[0].find(\"银行\") == -1:\n",
    "                       df.loc[index,'对手姓名'] = message_list[0]\n",
    "                    df.loc[index,'摘要'] = message_list[1]\n",
    "                if len(message_list) == 1:\n",
    "                    df.loc[index,'摘要'] = message_list[0]\n",
    "            zg_df = pd.concat([zg_df,df])\n",
    "zg_df.to_excel('zg.xlsx',index=False)\n",
    "\n",
    "zg_excel_last_df.to_excel('zg_excel_last.xlsx',index=False)\n",
    "zg_excle_df = zg_excle_df.reset_index(drop=True)\n",
    "zg_excle_df.to_excel('zg_excle.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41003900040014597-3-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-1-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-2-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-3-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-4-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-5-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-6-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040070059-7-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040072360-1-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41006900040072378-2-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41035600040017275-4-dgkh-cur_Password_Removed.xlsx\n",
      "F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件\\41035600040017481-5-dgkh-cur_Password_Removed.xlsx\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "target_path = r'F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\中国农业银行\\金管局\\金管局\\excel文件'\n",
    "pdf_df = pd.DataFrame()\n",
    "for root,dirs,files in os.walk(target_path):\n",
    "    for file in files:\n",
    "        if file.endswith('.xlsx') and file.find(\"~\")==-1:\n",
    "            file_path = os.path.join(root,file)\n",
    "            print(file_path)\n",
    "            df = pd.read_excel(file_path,header=6,dtype=str)\n",
    "            df['数据来源'] = file_path.replace(\".xlsx\",\".pdf\").replace(\"_Password_Removed\",\"\")\n",
    "            pdf_df = pd.concat([pdf_df,df])\n",
    "pdf_df = pdf_df[pdf_df['户名'].notna()]\n",
    "pdf_df['户名'].astype(str)\n",
    "pdf_df = pdf_df.replace(r'\\s+', '', regex=True)\n",
    "pdf_df.to_csv('./pdf.csv',index=False)\n",
    "\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['本方户名', '交易时间', '交易类型', '借贷标识', '交易金额', '交易余额', '对方账号', '对方户名', '交易备注',\n",
      "       '摘要', '本方开户行', '对手开户银行', '数据来源', '本方账号'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# 数据合并\n",
    "# 合并宁波银行和建设银行的数据\n",
    "df1 = pd.read_excel('./广东华兴银行银行交易流水数据.xlsx',dtype=str)\n",
    "df2 = pd.read_excel('./宁波银行银行交易流水数据.xlsx',dtype=str)\n",
    "df3 = pd.read_excel('./建设银行交易流水数据.xlsx',dtype=str)\n",
    "df4 = pd.read_excel('./zg_excle.xlsx',dtype=str)\n",
    "df5 = pd.read_excel('./zg_excel_last.xlsx',dtype=str)\n",
    "df6 = pd.read_excel('./zg.xlsx',dtype=str)\n",
    "df8 = pd.read_csv('./pdf.csv',dtype=str)\n",
    "\n",
    "def convert_to_time_format(num):  \n",
    "    # 将整数转换为字符串并填充为6位数  \n",
    "    time_str = str(num).zfill(6)  \n",
    "    \n",
    "    # 使用切片提取小时、分钟和秒  \n",
    "    hours = time_str[:-4]  # 前两位  \n",
    "    minutes = time_str[-4:-2]  # 中间两位  \n",
    "    seconds = time_str[-2:]  # 最后两位  \n",
    "    \n",
    "    # 格式化为 HH:MM:SS  \n",
    "    formatted_time = f\"{hours}:{minutes}:{seconds}\"  \n",
    "    return formatted_time  \n",
    "\n",
    "def handle_time(time):\n",
    "    time = time.strip()\n",
    "    if time.find('-')!=-1 and time.find(\":\")==-1:\n",
    "        return time + \" 00:00:00\"\n",
    "    if time.find('合计')!=-1:\n",
    "        return ''\n",
    "    if time.find('.')!=-1:\n",
    "        time = time[:time.find('.')]\n",
    "        return time\n",
    "    else:\n",
    "        return time\n",
    "# 合并元视界流水\n",
    "target_path = r\"F:\\工作任务\\2024-08-30元视界账户数据分析\\光盘\\光盘\\元视界\\交易明细表.xls\"\n",
    "df7 = pd.read_excel(target_path,dtype=str,sheet_name=\"交易明细表\",header=1)\n",
    "df7 = df7.replace(r'\\s+', '', regex=True)\n",
    "df7['数据来源'] = target_path\n",
    "df7 = df7[['*交易日期','*交易时间','*交易行名称','*账号','卡号','*账户名称','交易对方行名称','交易对方账号（卡号）','交易对方户名','*资金收付标识','*原币种交易金额','*账户余额','摘要说明','*交易方式标识','IP地址','MAC或IMEI地址','数据来源']]\n",
    "df7['交易时间'] = pd.to_datetime(df7['*交易日期'], format='%Y-%m-%d').dt.strftime('%Y-%m-%d')+' '+df7['*交易时间']\n",
    "df1.rename(columns={'交易日期/时间':'交易时间','交易类型（渠道）':'交易类型','余额':'交易余额','对手姓名':'对方户名','对手账号':'对方账号','借贷方向':'借贷标识'},inplace=True)\n",
    "\n",
    "df2.rename(columns={'账号':'本方账号','主帐户姓名':'本方户名','交易类型（渠道）':'交易类型','对手姓名':'对方户名','余额':'交易余额','交易日期/时间':'交易时间','借贷方向':'借贷标识','附言':'交易备注','对手账号':'对方账号'},inplace=True)\n",
    "\n",
    "df3.rename(columns={'摘要描述':'摘要','扩充备注':'交易备注','账户余额':'交易余额','交易机构名称':'对手开户银行','交易日期/时间':'交易时间'},inplace=True)\n",
    "\n",
    "df4.rename(columns={'客户名称':'本方户名','记账日':'交易时间','发生额':'交易金额','余额':'交易余额','对方账号名称':'对方户名','附言':'交易备注','账号':'本方账号'},inplace=True)\n",
    "\n",
    "df5.rename(columns={'账号':'本方账号','客户名':'本方户名','交易对手所在机构':'对手开户银行','交易类型':'借贷标识','交易后账户余额':'交易余额','交易对手姓名':'对方户名','交易对手账号':'对方账号','资金用途和来源':'摘要','交易日期/时间':'交易时间','ip地址':'IP地址'},inplace=True)\n",
    "\n",
    "df6.rename(columns={'账号':'本方账号','户名':'本方户名','日期':'交易时间','余额':'交易余额','对手姓名':'对方户名','对手账号':'对方账号','类型':'交易类型','对方信息':'交易备注'},inplace=True)\n",
    "\n",
    "df7.rename(columns={'*交易行名称':'本方开户行','*账号':'本方账号','卡号':'本方卡号','*账户名称':'本方户名','交易对方行名称':'对手开户银行','交易对方账号（卡号）':'对方账号','交易对方户名':'对方户名','*资金收付标识':'借贷标识','*原币种交易金额':'交易金额','*账户余额':'交易余额','摘要说明':'摘要','*交易方式标识':'交易类型','MAC或IMEI地址':'mac地址'},inplace=True)\n",
    "# df9 = df8[df8['交易日期'].str.contains('.0',na=False,regex=False,case=False)]\n",
    "# print(df9)\n",
    "df8['交易时间'] = df8['交易时间'].apply(convert_to_time_format)\n",
    "df8['交易日期/时间'] = pd.to_datetime(df8['交易日期'], format='%Y%m%d').dt.strftime('%Y-%m-%d')+' '+df8['交易时间']\n",
    "df8.drop(columns={'交易日期','交易时间'},inplace=True)\n",
    "df8.rename(columns={'户名':'本方户名','账号':'本方账号','交易行名':'本方开户行','借贷标志':'借贷标识','交易后余额':'交易余额','对手方账号':'对方账号','对手方户名':'对方户名','对手方行名':'对手开户银行','交易日期/时间':'交易时间'},inplace=True)\n",
    "\n",
    "df1.drop(columns={'借方','贷方'},inplace=True)\n",
    "#df2.drop(columns={'子账号'},inplace=True)\n",
    "df3.drop(columns={'明细号','借方发生额','贷方发生额','交易机构号','对方行名','柜员号','交易流水号','客户编号'},inplace=True)\n",
    "# 终端标识\t操作系统信息\n",
    "df4.drop(columns={'交易流水号','用途'},inplace=True)\n",
    "df5.drop(columns={'操作系统信息','终端标识','账户行','交易币种'},inplace=True)\n",
    "df6.drop(columns={'借方发生额','贷方发生额','交易流水号'},inplace=True)\n",
    "df7.drop(columns={'*交易日期','*交易时间','*交易时间'},inplace=True)\n",
    "df8.drop(columns={'序号','币种','交易日志号','传票号','交易码'},inplace=True)\n",
    "print(df4.columns)\n",
    "# # 统计列名出现次数  \n",
    "# counts = df8.columns.value_counts()  \n",
    "\n",
    "# # 检查是否存在重复的列名  \n",
    "# has_duplicates = any(counts > 1)  \n",
    "\n",
    "# print(\"是否存在重复的列名:\", has_duplicates) \n",
    "\n",
    "rs = pd.concat([df1,df2,df3,df4,df5,df6,df7,df8]) \n",
    "rs['交易时间'] = rs['交易时间'].apply(handle_time)\n",
    "rs['交易时间'] = pd.to_datetime(rs['交易时间'], format='%Y-%m-%d %H:%M:%S')\n",
    "# 本方户名\t本方账号\t交易时间\t摘要\t借贷标识\t交易金额\t交易余额\t对方户名\t对方账号\t对手开户银行\t交易类型\t数据来源\t本方开户行\n",
    "rs.drop_duplicates(subset=['本方户名','本方账号','交易时间','交易金额','交易余额','对方户名','对方账号'],inplace=True)\n",
    "rs_jy = rs[rs['本方户名']!=rs['对方户名']]\n",
    "# 去重\n",
    "rs.drop_duplicates(inplace=True)\n",
    "rs.to_excel('./rs.xlsx', index=False)\n",
    "rs_jy.to_excel('./rs_jy.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#深圳市前海茂圣资产管理有限公司 深圳嘉银基金管理有限公司  深圳市中能国泰信息咨询有限公司\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始 DataFrame:\n",
      "  array_column  field1  field2\n",
      "0       [1, 2]       1       2\n",
      "1       [3, 4]       3       4\n",
      "2       [5, 6]       5       6\n",
      "3       [7, 8]       9       8\n",
      "\n",
      "更新后的 DataFrame（包含存在标识）：\n",
      "  array_column  field1  field2  exists_in_fields\n",
      "0       [1, 2]       1       2              True\n",
      "1       [3, 4]       3       4              True\n",
      "2       [5, 6]       5       6              True\n",
      "3       [7, 8]       9       8              True\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "import numpy as np  \n",
    "\n",
    "# 创建示例 DataFrame  \n",
    "data = {  \n",
    "    'array_column': [[1, 2], [3, 4], [5, 6], [7, 8]],  # 包含数组的列  \n",
    "    'field1': [1, 3, 5, 9],  # 第一个比较字段  \n",
    "    'field2': [2, 4, 6, 8]   # 第二个比较字段  \n",
    "}  \n",
    "df = pd.DataFrame(data)  \n",
    "\n",
    "print(\"原始 DataFrame:\")  \n",
    "print(df)  \n",
    "\n",
    "# 定义一个函数来检查数组元素是否存在于 field1 或 field2 中  \n",
    "def check_in_fields(arr):  \n",
    "    return any(element in df['field1'].values for element in arr) or any(element in df['field2'].values for element in arr)  \n",
    "\n",
    "# 应用函数并创建新的列来存储结果  \n",
    "df['exists_in_fields'] = df['array_column'].apply(check_in_fields)  \n",
    "\n",
    "print(\"\\n更新后的 DataFrame（包含存在标识）：\")  \n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始 DataFrame:\n",
      "  array_column  field1  field2\n",
      "0       [1, 2]       1       2\n",
      "1       [3, 4]       3       4\n",
      "2       [5, 6]       5       6\n",
      "3       [7, 8]       9       8\n",
      "\n",
      "更新后的 DataFrame（包含找到的元素）：\n",
      "  array_column  field1  field2 found_in_fields\n",
      "0       [1, 2]       1       2          [1, 2]\n",
      "1       [3, 4]       3       4          [3, 4]\n",
      "2       [5, 6]       5       6          [5, 6]\n",
      "3       [7, 8]       9       8             [8]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "\n",
    "# 创建示例 DataFrame  \n",
    "data = {  \n",
    "    'array_column': [[1, 2], [3, 4], [5, 6], [7, 8]],  # 包含数组的列  \n",
    "    'field1': [1, 3, 5, 9],  # 第一个比较字段  \n",
    "    'field2': [2, 4, 6, 8]   # 第二个比较字段  \n",
    "}  \n",
    "df = pd.DataFrame(data)  \n",
    "\n",
    "print(\"原始 DataFrame:\")  \n",
    "print(df)  \n",
    "\n",
    "# 定义一个函数来检查数组元素是否存在于 field1 或 field2 中，并返回符合条件的元素  \n",
    "def find_in_fields(arr):  \n",
    "    found_values = []  \n",
    "    for element in arr:  \n",
    "        if element in df['field1'].values or element in df['field2'].values:  \n",
    "            found_values.append(element)  \n",
    "    return found_values  \n",
    "\n",
    "# 应用函数并创建新的列来存储结果  \n",
    "df['found_in_fields'] = df['array_column'].apply(find_in_fields)  \n",
    "\n",
    "print(\"\\n更新后的 DataFrame（包含找到的元素）：\")  \n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始 DataFrame:\n",
      "  array_column array_column2  index\n",
      "0       [1, 2]     [2, 4, 6]      1\n",
      "1       [3, 4]     [4, 5, 9]      3\n",
      "2       [5, 6]     [6, 9, 1]      5\n",
      "3       [7, 8]     [2, 8, 9]      9\n",
      "\n",
      "交集结果：\n",
      "   index array_column array_column2 intersection_arrays\n",
      "0      1       [1, 2]     [2, 4, 6]                 [2]\n",
      "1      3       [3, 4]     [4, 5, 9]                 [4]\n",
      "2      5       [5, 6]     [6, 9, 1]                 [6]\n",
      "3      9       [7, 8]     [2, 8, 9]                 [8]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  \n",
    "\n",
    "# 创建示例 DataFrame  \n",
    "data = {  \n",
    "    'array_column': [[1, 2], [3, 4], [5, 6], [7, 8]],  # 包含数组的列  \n",
    "    'array_column2': [[2, 4, 6], [4, 5, 9], [6, 9, 1], [2, 8, 9]],  # 第二个数组列  \n",
    "    'index': [1, 3, 5, 9]   # 第一个比较字段  \n",
    "}  \n",
    "df = pd.DataFrame(data)  \n",
    "\n",
    "print(\"原始 DataFrame:\")  \n",
    "print(df)  \n",
    "\n",
    "# 定义一个函数来获取index对应的array_column和array_column2的交集  \n",
    "def intersection_for_index(row):  \n",
    "    # 获取当前行的index对应的array_column和array_column2  \n",
    "    array1 = row['array_column']  \n",
    "    array2 = row['array_column2']  \n",
    "    # 计算交集并返回  \n",
    "    return list(set(array1).intersection(set(array2)))  \n",
    "\n",
    "# 创建新的列来存储交集结果  \n",
    "df['intersection_arrays'] = df.apply(intersection_for_index, axis=1)  \n",
    "\n",
    "print(\"\\n交集结果：\")  \n",
    "print(df[['index', 'array_column', 'array_column2', 'intersection_arrays']])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
